﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VAPKOPF
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {
            #region
            #endregion
            switch (app.company)
            {
                case "YGR":
                case "SXN":
                case "TST":
                default:
                    #region
                    dwReturn["APKMANDANT"] = "00";
                    dwReturn["APKWERK"] = "   ";
                    dwReturn["APKANRKRS"] = "SY";
                    dwReturn["APKALFDNR"] = "000001";
                    dwReturn["APKSPEZIF"] = "³£ÖÝÈýÔ´È¾É«Á÷³Ì                                  ";
                    dwReturn["APKSPSTA"] = "N";
                    dwReturn["APKSPGRD"] = "                              ";
                    dwReturn["APKSTUFE"] = "G";
                    dwReturn["APKABT"] = "   ";
                    dwReturn["APKVERSAE"] = "J";
                    dwReturn["APKVERSZL"] = "0";
                    dwReturn["APKAENDZL"] = "2";
                    dwReturn["APKAENDBEN"] = "        ";
                    dwReturn["APKAENDDAT"] = "0";
                    dwReturn["APKNEUBEN"] = "JAKV    ";
                    dwReturn["APKNEUDAT"] = "20140920";
                    dwReturn["APKZUSTAND"] = "    ";
                    dwReturn["APKZUSATZKZ1"] = " ";
                    dwReturn["APKZUSATZKZ2"] = " ";
                    dwReturn["APKZUSATZKZ3"] = " ";
                    dwReturn["APKZUSATZKZ4"] = " ";
                    dwReturn["APKGZUSKZ1"] = " ";
                    dwReturn["APKGZUSKZ2"] = " ";
                    dwReturn["APKGZUSKZ3"] = " ";
                    dwReturn["APKGZUSKZ4"] = " ";
                    dwReturn["APKKAAGRA"] = "     ";
                    dwReturn["APKKAAGRP"] = "  ";
                    dwReturn["APKGRGRP1"] = "   ";
                    dwReturn["APKGRGRP2"] = "   ";
                    dwReturn["APKGRGRP3"] = "   ";
                    dwReturn["APKGRGRP4"] = "   ";
                    dwReturn["APKGRGRP5"] = "   ";
                    dwReturn["APKGSTUFE"] = "  ";
                    dwReturn["APKGRRAS"] = "   ";
                    dwReturn["APKTLVF1"] = "   ";
                    dwReturn["APKTLAFOV1"] = "0";
                    dwReturn["APKTLAFOB1"] = "0";
                    dwReturn["APKTLVF2"] = "   ";
                    dwReturn["APKTLAFOV2"] = "0";
                    dwReturn["APKTLAFOB2"] = "0";
                    dwReturn["APKTLVF3"] = "   ";
                    dwReturn["APKTLAFOV3"] = "0";
                    dwReturn["APKTLAFOB3"] = "0";
                    dwReturn["APKTLVF4"] = "   ";
                    dwReturn["APKTLAFOV4"] = "0";
                    dwReturn["APKTLAFOB4"] = "0";
                    dwReturn["APKTLVF5"] = "   ";
                    dwReturn["APKTLAFOV5"] = "0";
                    dwReturn["APKTLAFOB5"] = "0";
                    dwReturn["APKTLVF6"] = "   ";
                    dwReturn["APKTLAFOV6"] = "0";
                    dwReturn["APKTLAFOB6"] = "0";
                    dwReturn["APKTLVF7"] = "   ";
                    dwReturn["APKTLAFOV7"] = "0";
                    dwReturn["APKTLAFOB7"] = "0";
                    dwReturn["APKMU"] = " ";
                    dwReturn["APKMURLOD"] = "00";
                    dwReturn["APKMUPROD"] = "00";
                    dwReturn["APKMUCTOD"] = " ";
                    dwReturn["APKMUDTRL"] = "0";
                    dwReturn["APKMUBIRL"] = "        ";
                    dwReturn["APKVERSZLRL"] = "0";
                    dwReturn["APKVERSBEN"] = "        ";
                    dwReturn["APKLO"] = "  ";
                    dwReturn["APKEXPROD"] = " ";
                    dwReturn["APKKART1"] = "  ";
                    dwReturn["APKKZMIN1"] = "0";
                    dwReturn["APKKZMAX1"] = "0";
                    dwReturn["APKKMINWMGE1"] = "0";
                    dwReturn["APKKABST1"] = "0";
                    dwReturn["APKKZE1"] = "  ";
                    dwReturn["APKKART2"] = "  ";
                    dwReturn["APKKZMIN2"] = "0";
                    dwReturn["APKKZMAX2"] = "0";
                    dwReturn["APKKMINWMGE2"] = "0";
                    dwReturn["APKKABST2"] = "0";
                    dwReturn["APKKZE2"] = "  ";
                    dwReturn["APKFILLER"] = "                                  ";
                    #endregion
                    break;
            }
        }
        public void InsertAPKOPF(DataRow dwData, string ITXDBVERSION = "50.24")
        {

            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "YGR":
                case "TST":
                case "SXN":
                    or1.CommandText = "INSERT INTO APKOPF ( APKMANDANT,APKWERK,APKANRKRS,APKALFDNR,APKSPEZIF,APKSPSTA,APKSPGRD,APKSTUFE,APKABT,APKVERSAE,APKVERSZL,APKAENDZL,APKAENDBEN,APKAENDDAT,APKNEUBEN,APKNEUDAT,APKZUSTAND,APKZUSATZKZ1,APKZUSATZKZ2,APKZUSATZKZ3,APKZUSATZKZ4,APKGZUSKZ1,APKGZUSKZ2,APKGZUSKZ3,APKGZUSKZ4,APKKAAGRA,APKKAAGRP,APKGRGRP1,APKGRGRP2,APKGRGRP3,APKGRGRP4,APKGRGRP5,APKGSTUFE,APKGRRAS,APKTLVF1,APKTLAFOV1,APKTLAFOB1,APKTLVF2,APKTLAFOV2,APKTLAFOB2,APKTLVF3,APKTLAFOV3,APKTLAFOB3,APKTLVF4,APKTLAFOV4,APKTLAFOB4,APKTLVF5,APKTLAFOV5,APKTLAFOB5,APKTLVF6,APKTLAFOV6,APKTLAFOB6,APKTLVF7,APKTLAFOV7,APKTLAFOB7,APKMU,APKMURLOD,APKMUPROD,APKMUCTOD,APKMUDTRL,APKMUBIRL,APKVERSZLRL,APKVERSBEN,APKLO,APKEXPROD,APKKART1,APKKZMIN1,APKKZMAX1,APKKMINWMGE1,APKKABST1,APKKZE1,APKKART2,APKKZMIN2,APKKZMAX2,APKKMINWMGE2,APKKABST2,APKKZE2,APKFILLER)"
                + " VALUES (:APKMANDANT:,:APKWERK:,:APKANRKRS:,:APKALFDNR:,:APKSPEZIF:,:APKSPSTA:,:APKSPGRD:,:APKSTUFE:,:APKABT:,:APKVERSAE:,:APKVERSZL:,:APKAENDZL:,:APKAENDBEN:,:APKAENDDAT:,:APKNEUBEN:,:APKNEUDAT:,:APKZUSTAND:,:APKZUSATZKZ1:,:APKZUSATZKZ2:,:APKZUSATZKZ3:,:APKZUSATZKZ4:,:APKGZUSKZ1:,:APKGZUSKZ2:,:APKGZUSKZ3:,:APKGZUSKZ4:,:APKKAAGRA:,:APKKAAGRP:,:APKGRGRP1:,:APKGRGRP2:,:APKGRGRP3:,:APKGRGRP4:,:APKGRGRP5:,:APKGSTUFE:,:APKGRRAS:,:APKTLVF1:,:APKTLAFOV1:,:APKTLAFOB1:,:APKTLVF2:,:APKTLAFOV2:,:APKTLAFOB2:,:APKTLVF3:,:APKTLAFOV3:,:APKTLAFOB3:,:APKTLVF4:,:APKTLAFOV4:,:APKTLAFOB4:,:APKTLVF5:,:APKTLAFOV5:,:APKTLAFOB5:,:APKTLVF6:,:APKTLAFOV6:,:APKTLAFOB6:,:APKTLVF7:,:APKTLAFOV7:,:APKTLAFOB7:,:APKMU:,:APKMURLOD:,:APKMUPROD:,:APKMUCTOD:,:APKMUDTRL:,:APKMUBIRL:,:APKVERSZLRL:,:APKVERSBEN:,:APKLO:,:APKEXPROD:,:APKKART1:,:APKKZMIN1:,:APKKZMAX1:,:APKKMINWMGE1:,:APKKABST1:,:APKKZE1:,:APKKART2:,:APKKZMIN2:,:APKKZMAX2:,:APKKMINWMGE2:,:APKKABST2:,:APKKZE2:,:APKFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break; 
            } 
            Connect2Proxy(or1);
        }
        public void UpdateAPKOPF(DataRow dwData, int option = 0)
        {

            or1 = OracleDB.CreateCommand();
            if (option == 0)
            {
                switch (app.company)
                {
                    default:
                    case "TST":
                    case "YGR":
                    case "SXN":
                        option = 1;
                        break;
                }
                switch (option)
                {
                    case 1:
                        UAPK01(dwData);
                        break;
                }
            }
        }
        public void UAPK01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE APKOPF SET APKMANDANT=:APKMANDANT:,APKWERK=:APKWERK:,APKANRKRS=:APKANRKRS:,APKALFDNR=:APKALFDNR:,APKSPEZIF=:APKSPEZIF:,APKSPSTA=:APKSPSTA:,APKSPGRD=:APKSPGRD:,APKSTUFE=:APKSTUFE:,APKABT=:APKABT:,APKVERSAE=:APKVERSAE:,APKVERSZL=:APKVERSZL:,APKAENDZL=:APKAENDZL:,APKAENDBEN=:APKAENDBEN:,APKAENDDAT=:APKAENDDAT:,APKNEUBEN=:APKNEUBEN:,APKNEUDAT=:APKNEUDAT:,APKZUSTAND=:APKZUSTAND:,APKZUSATZKZ1=:APKZUSATZKZ1:,APKZUSATZKZ2=:APKZUSATZKZ2:,APKZUSATZKZ3=:APKZUSATZKZ3:,APKZUSATZKZ4=:APKZUSATZKZ4:,APKGZUSKZ1=:APKGZUSKZ1:,APKGZUSKZ2=:APKGZUSKZ2:,APKGZUSKZ3=:APKGZUSKZ3:,APKGZUSKZ4=:APKGZUSKZ4:,APKKAAGRA=:APKKAAGRA:,APKKAAGRP=:APKKAAGRP:,APKGRGRP1=:APKGRGRP1:,APKGRGRP2=:APKGRGRP2:,APKGRGRP3=:APKGRGRP3:,APKGRGRP4=:APKGRGRP4:,APKGRGRP5=:APKGRGRP5:,APKGSTUFE=:APKGSTUFE:,APKGRRAS=:APKGRRAS:,APKTLVF1=:APKTLVF1:,APKTLAFOV1=:APKTLAFOV1:,APKTLAFOB1=:APKTLAFOB1:,APKTLVF2=:APKTLVF2:,APKTLAFOV2=:APKTLAFOV2:,APKTLAFOB2=:APKTLAFOB2:,APKTLVF3=:APKTLVF3:,APKTLAFOV3=:APKTLAFOV3:,APKTLAFOB3=:APKTLAFOB3:,APKTLVF4=:APKTLVF4:,APKTLAFOV4=:APKTLAFOV4:,APKTLAFOB4=:APKTLAFOB4:,APKTLVF5=:APKTLVF5:,APKTLAFOV5=:APKTLAFOV5:,APKTLAFOB5=:APKTLAFOB5:,APKTLVF6=:APKTLVF6:,APKTLAFOV6=:APKTLAFOV6:,APKTLAFOB6=:APKTLAFOB6:,APKTLVF7=:APKTLVF7:,APKTLAFOV7=:APKTLAFOV7:,APKTLAFOB7=:APKTLAFOB7:,APKMU=:APKMU:,APKMURLOD=:APKMURLOD:,APKMUPROD=:APKMUPROD:,APKMUCTOD=:APKMUCTOD:,APKMUDTRL=:APKMUDTRL:,APKMUBIRL=:APKMUBIRL:,APKVERSZLRL=:APKVERSZLRL:,APKVERSBEN=:APKVERSBEN:,APKLO=:APKLO:,APKEXPROD=:APKEXPROD:,APKKART1=:APKKART1:,APKKZMIN1=:APKKZMIN1:,APKKZMAX1=:APKKZMAX1:,APKKMINWMGE1=:APKKMINWMGE1:,APKKABST1=:APKKABST1:,APKKZE1=:APKKZE1:,APKKART2=:APKKART2:,APKKZMIN2=:APKKZMIN2:,APKKZMAX2=:APKKZMAX2:,APKKMINWMGE2=:APKKMINWMGE2:,APKKABST2=:APKKABST2:,APKKZE2=:APKKZE2:,APKFILLER=:APKFILLER:"
                + " WHERE  APKMANDANT =:APKMANDANT: AND APKANRKRS =:APKANRKRS: AND APKALFDNR =:APKALFDNR:"
           ;
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
        public DataSet QueryAPKOPF(string apkrs, string apdnr)
        {
            // APZMANDANT  APZART APZVAR APZANRKRS APZALFDNR APZSTUFE

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM APKOPF WHERE  "
            + "  APKMANDANT='00' AND APKANRKRS=:APKANRKRS AND APKALFDNR=:APKALFDNR";

            or1.Parameters.Add("APKANRKRS", OracleType.NVarChar).Value = apkrs;
            or1.Parameters.Add("APKALFDNR", OracleType.NVarChar).Value = apdnr;

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "APKOPF";
            return SResult;
        }
        public DataSet QueryAPKOPF(DataRow dwData, int option = 1)
        {


            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0: SResult = QAPK00(dwData); break;
                case 1: SResult = QAPK01(dwData); break;
                case 2: SResult = QAPK02(dwData); break;
                case 9999: SResult = QAPK99(dwData); break;
            }
            return SResult;
        }
        public DataSet QAPK00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM APKOPF WHERE  ROWNUM<2 ";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "APKOPF";
            return SResult;
        }
        public DataSet QAPK01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT P.* FROM APKOPF P  WHERE APKMANDANT=:APKMANDANT: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "APKOPF";
            return SResult;
        }
        public DataSet QAPK02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT '' AS DES,APKANRKRS,APKALFDNR, APKANRKRS||APKALFDNR AS KEY,APKSPEZIF FROM APKOPF P  WHERE APKMANDANT=:APKMANDANT: AND APKANRKRS=:APKANRKRS: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "APKOPF";
            return SResult;
        }
        public DataSet QAPK03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT APKSPEZIF AS DES,APKSPEZIF,APKANRKRS,APKALFDNR, APKANRKRS||APKALFDNR AS KEY,APKZUSATZKZ1||APKZUSATZKZ2||APKZUSATZKZ3||APKZUSATZKZ4 AS INMODE, APKGZUSKZ1||APKGZUSKZ2||APKGZUSKZ3||APKGZUSKZ4 AS OUTMODE"
                + " FROM APKOPF P   WHERE APKMANDANT=:APKMANDANT: "
                + dwData["APKANRKRS"].ToString()
                + dwData["APKALFDNR"].ToString()
                + dwData["APKSTUFE"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "APKOPF";
            return SResult;
        }
        public DataSet QAPK031(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT APKSPEZIF AS DES,APKSPEZIF,APKANRKRS,APKALFDNR, APKANRKRS||APKALFDNR AS KEY,APKZUSATZKZ1||APKZUSATZKZ2||APKZUSATZKZ3||APKZUSATZKZ4 AS INMODE, APKGZUSKZ1||APKGZUSKZ2||APKGZUSKZ3||APKGZUSKZ4 AS OUTMODE,P.* "
                + " FROM APKOPF P   WHERE APKMANDANT=:APKMANDANT: "
                + dwData["APKANRKRS"].ToString()
                + dwData["APKALFDNR"].ToString()
                + dwData["APKSTUFE"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "APKOPF";
            return SResult;
        }
        public DataSet QAPK04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT APKSPEZIF AS DES,APKANRKRS,APKALFDNR, APKANRKRS||APKALFDNR AS KEY,P.* FROM APKOPF P  "
                + " WHERE APKMANDANT=:APKMANDANT: AND APKANRKRS=:APKANRKRS: AND APKALFDNR=:APKALFDNR:  "
               ;
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QAPK99(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM APKOPF ORDER BY 1,2,3,4";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = "APKOPF";
            return SResult;
        }

        public void DAPK01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM APKOPF   WHERE  APKMANDANT=:APKMANDANT: AND APKANRKRS=:APKANRKRS: AND APKALFDNR =:APKALFDNR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DAPK02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM APKOPF   WHERE  APKMANDANT=:APKMANDANT: AND APKANRKRS=:APKANRKRS: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DAPK00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM APKOPF   WHERE  APKMANDANT=:APKMANDANT:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }

    }
}
